﻿IF NOT EXISTS (SELECT * FROM sys.change_tracking_databases WHERE database_id = DB_ID(N'skyschool')) 
   ALTER DATABASE [skynetlibrary] 
   SET  CHANGE_TRACKING = on
GO
IF NOT EXISTS (SELECT * FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID(N'[dbo].[Students]')) 
   ALTER TABLE [dbo].Accessibility 
   enable CHANGE_TRACKING
GO

create table Accessibility
(
	id uniqueidentifier primary key, 
	[application] varchar(256), 
	userid uniqueidentifier,	
	kind varchar(20),
	code varchar(128),
	code1 varchar(128),
	code2 varchar(128),
	code3 varchar(128),
	code4 varchar(128),
	code5 varchar(128),
	code6 varchar(128),
	code7 varchar(128),
	code8 varchar(128),
	code9 varchar(128),
	code10 varchar(128),
	code11 varchar(128),
	code12 varchar(128),
	code13 varchar(128),
	code14 varchar(128),
	code15 varchar(128),
	value varchar(1024),
	defaultvalue varchar(1024)	
)
go
create proc usp_accssesibility_SelectIncrementalInsertsCommand
@CurrentUserId uniqueidentifier,
@sync_initialized bit , 
@sync_last_received_anchor bigint,
@sync_client_id_binary varbinary(128),
@sync_new_received_anchor bigint
as
IF @sync_initialized = 0 
	SELECT dbo.Accessibility.[Id], [application], userid, kind, code, code1, code2, code3, code4, code5, code6, code7, code8, code9 , code10, code11, code12, code13, code14, code15, value, defaultvalue 
	FROM dbo.Accessibility LEFT OUTER JOIN 
		 CHANGETABLE(CHANGES dbo.Accessibility, @sync_last_received_anchor) CT 
		 ON CT.[Id] = dbo.Accessibility.[Id] 
	WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) 
		   and dbo.Accessibility.[userid] = @CurrentUserId
ELSE  
	BEGIN 
		SELECT dbo.Accessibility.[Id], [application], userid,kind, code, code1, code2, code3, code4, code5, code6, code7, code8, code9 , code10, code11, code12, code13, code14, code15, value, defaultvalue 
		FROM dbo.Accessibility JOIN 
		CHANGETABLE(CHANGES dbo.Accessibility, @sync_last_received_anchor) CT ON CT.[Id] = dbo.Accessibility.[Id] 
		WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION  <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary))
			 and dbo.Accessibility.[userid] = @CurrentUserId; 
		
		IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Accssesibility')) > @sync_last_received_anchor 
			RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Accessibility')  
	END 	
GO
Create proc usp_accssesibility_SelectIncrementalUpdatesCommand
@sync_initialized bit , 
@sync_last_received_anchor bigint,
@sync_new_received_anchor bigint,
@sync_client_id_binary varbinary(128)
as
IF @sync_initialized > 0  
BEGIN 
	SELECT dbo.Accessibility.[Id], [application], userid,kind, code, code1, code2, code3, code4, code5, code6, code7, code8, code9 , code10, code11, code12, code13, code14, code15, value, defaultvalue 
	FROM dbo.Accessibility JOIN CHANGETABLE(CHANGES dbo.Accessibility, @sync_last_received_anchor) CT ON CT.[Id] = dbo.Accessibility.[Id] 
	WHERE (CT.SYS_CHANGE_OPERATION = 'U' AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); 
	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Accessibility')) > @sync_last_received_anchor 
		RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Accessibility')  
END 


go
create  proc usp_accssesibility_SelectIncrementalDeletesCommand
@sync_initialized bit , 
@sync_last_received_anchor bigint,
@sync_new_received_anchor bigint,
@sync_client_id_binary varbinary(128)
as
IF @sync_initialized > 0  
BEGIN 
	SELECT CT.[Id] 
	FROM CHANGETABLE(CHANGES dbo.Accessibility, @sync_last_received_anchor) CT 
	WHERE (CT.SYS_CHANGE_OPERATION = 'D' 
		AND CT.SYS_CHANGE_VERSION <= @sync_new_received_anchor 
		AND (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary)); 
	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Students')) > @sync_last_received_anchor 
		RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Accessibility')  
END 


go

create proc usp_accssesibility_InsertCommand
@sync_client_id_binary varbinary(128),
@Id uniqueidentifier,
@application varchar(256), 
@userid uniqueidentifier,	
@kind varchar(20),
@code varchar(128),
@code1 varchar(128),
@code2 varchar(128),
@code3 varchar(128),
@code4 varchar(128),
@code5 varchar(128),
@code6 varchar(128),
@code7 varchar(128),
@code8 varchar(128),
@code9 varchar(128),
@code10 varchar(128),
@code11 varchar(128),
@code12 varchar(128),
@code13 varchar(128),
@code14 varchar(128),
@code15 varchar(128),
@value varchar(1024),
@defaultvalue varchar(1024),
@sync_row_count int OUTPUT,
@sync_last_received_anchor bigint
as
	WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) 
		INSERT INTO dbo.Accessibility( [Id], [application], [userid],kind, code, code1, code2, code3, code4, code5, code6, code7, code8, code9, code10, code11, code12, code13, code14, code15, value, defaultvalue) 
		VALUES (@Id, @application, @userid, @kind, @code, @code1,@code2,@code3,@code4,@code5,@code6,@code7,@code8,@code9,@code10,@code11,@code12,@code13,@code14,@code15,@value, @defaultvalue)
	 SET @sync_row_count = @@rowcount; 
	 IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Accessibility')) > @sync_last_received_anchor 
		RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Accessibility') 
		
	
	
	

	go
create   proc usp_accssesibility_DeleteCommand
@sync_client_id_binary varbinary(128),
@Id uniqueidentifier,
@sync_force_write bit,
@sync_last_received_anchor bigint,
@sync_row_count int OUTPUT
as
WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) 
	DELETE dbo.Accessibility 
	FROM dbo.Accessibility JOIN 
		CHANGETABLE(VERSION dbo.Accessibility, ([Id]), (@Id)) CT  ON CT.[Id] = dbo.Accessibility.[Id] 
	WHERE (@sync_force_write = 1 OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) 
	SET @sync_row_count = @@rowcount; 
	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Accessibility')) > @sync_last_received_anchor 
		RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Accessibility')
		
		
		go
create proc usp_accssesibility_UpdateCommand
@Id uniqueidentifier,
@application varchar(256), 
@userid uniqueidentifier,	
@kind varchar(20),
@code varchar(128),
@code1 varchar(128),
@code2 varchar(128),
@code3 varchar(128),
@code4 varchar(128),
@code5 varchar(128),
@code6 varchar(128),
@code7 varchar(128),
@code8 varchar(128),
@code9 varchar(128),
@code10 varchar(128),
@code11 varchar(128),
@code12 varchar(128),
@code13 varchar(128),
@code14 varchar(128),
@code15 varchar(128),
@value varchar(1024),
@defaultvalue varchar(1024),
@sync_client_id_binary varbinary(128),
@sync_force_write bit,
@sync_last_received_anchor bigint,
@sync_row_count int OUTPUT
as
WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary) 
	UPDATE dbo.Accessibility 
	SET [application] = @application, userid= @userid, kind=@kind, [Code] = @Code, code1=@code1, code2=@code2, code3= @code3, code4=@code4, code5 = @code5, code6=@code6, code7=@code7, code8=@code8, code9=@code9, code10=@code10, code11=@code11, code12 =@code12, code13=@code13, code14= @code14 , code15=@code15, value =@value, defaultvalue =@defaultvalue
	FROM dbo.Accessibility  JOIN CHANGETABLE(VERSION dbo.Accessibility , ([Id]), (@Id)) CT  ON CT.[Id] = dbo.Accessibility.[Id] 
		WHERE (@sync_force_write = 1 OR CT.SYS_CHANGE_VERSION IS NULL OR CT.SYS_CHANGE_VERSION <= @sync_last_received_anchor OR (CT.SYS_CHANGE_CONTEXT IS NOT NULL AND CT.SYS_CHANGE_CONTEXT = @sync_client_id_binary)) 
	SET @sync_row_count = @@rowcount; 
	IF CHANGE_TRACKING_MIN_VALID_VERSION(object_id(N'dbo.Accessibility ')) > @sync_last_received_anchor 
		RAISERROR (N'SQL Server Change Tracking has cleaned up tracking information for table ''%s''. To recover from this error, the client must reinitialize its local database and try again',16,3,N'dbo.Accessibility') 


		
		
